// PMS数据库操作（基于mysql）
package daos

import (
	"bsm/logger"
	"bsm/models"
	"fmt"

	"github.com/jmoiron/sqlx"
)

// 定义DAO接口
type PmsDao interface {
	GetAllOrg() ([]models.Org, error)
	GetUsersByOrg(orgCode string) ([]models.ProjectUser, error)
	GetProjectDiffs(pcode string) ([]models.ProjectDiff, error)
	GetProjectDiffDetails(pcode string) ([]models.ProjectDiffDetail, error)

	// 其他数据访问方法...
	Close() error
}

// 实现接口
type pmsDaoImpl struct {
	dbPms *sqlx.DB
}

func NewPmsDao(dbPMS *sqlx.DB) PmsDao {
	return &pmsDaoImpl{
		dbPms: dbPMS,
	}
}

// 关闭数据库连接
func (dao *pmsDaoImpl) Close() error {
	var errs []error

	if err := dao.dbPms.Close(); err != nil {
		errs = append(errs, err)
	}

	if len(errs) > 0 {
		logger.Errorf("errors closing DB connections: %v", errs)
		return fmt.Errorf("errors closing DB connections: %v", errs)
	}
	return nil
}

// 业务相关方法（公有）

func (dao *pmsDaoImpl) GetAllOrg() ([]models.Org, error) {
	const query = "SELECT * FROM bsm_org where org_inuse=1"

	var owners []models.Org
	err := dao.dbPms.Select(&owners, query)
	if err != nil {
		logger.Errorf("GetAllOrg failed - error: %v", err)
		return nil, err
	}
	return owners, nil
}

func (dao *pmsDaoImpl) GetProjectDiffs(dcode string) ([]models.ProjectDiff, error) {
	query := `SELECT DISTINCT d.Design_Code,a.ProjectName as Project_Name,a.BudgetTotal as Budget_Total,
		a.AcceptTotal as Accept_Total,a.AcceptTotal-a.BudgetTotal as Diff_Total,
		Round((a.AcceptTotal-a.BudgetTotal)/a.BudgetTotal*100,2) as Diff_Rate 
		FROM Project_Design d left join audit_standing_book a on d.Design_Code=a.ProjectCode`

	if dcode != "" {
		query += " WHERE ProjectCode=?"
	}
	query += " ORDER BY Diff_Rate"

	var diffs []models.ProjectDiff
	var err error
	if dcode != "" {
		err = dao.dbPms.Select(&diffs, query, dcode)
	} else {
		err = dao.dbPms.Select(&diffs, query)
	}

	if err != nil {
		logger.Errorf("GetProjectDiffs failed - dcode: %s, error: %v", dcode, err)
		return nil, err
	}
	return diffs, nil
}

func (dao *pmsDaoImpl) GetProjectDiffDetails(pcode string) ([]models.ProjectDiffDetail, error) {
	const query = `SELECT Design_Code,Code,Name,Item_Type,Remark,
		SUM(CASE WHEN Design_Type = 1 THEN Count ELSE 0 END) AS Budget_Count,
		SUM(CASE WHEN Design_Type = 2 THEN Count ELSE 0 END) AS Accept_Count,
		SUM(CASE WHEN Design_Type = 2 THEN Count ELSE 0 END) - 
		SUM(CASE WHEN Design_Type = 1 THEN Count ELSE 0 END) AS Diff,
		CASE 
			WHEN SUM(CASE WHEN Design_Type = 2 THEN Count ELSE 0 END) - 
				 SUM(CASE WHEN Design_Type = 1 THEN Count ELSE 0 END) > 0 THEN '超支'
			WHEN SUM(CASE WHEN Design_Type = 2 THEN Count ELSE 0 END) - 
				 SUM(CASE WHEN Design_Type = 1 THEN Count ELSE 0 END) < 0 THEN '节余'
			ELSE '相同'
		END AS Diff_Status
	FROM project_design
	WHERE Design_Code like ?
	GROUP BY Design_Code,Code,Name,Item_Type 
	ORDER BY Design_Code,Code`

	var details []models.ProjectDiffDetail
	err := dao.dbPms.Select(&details, query, pcode)
	if err != nil {
		logger.Errorf("GetProjectDiffDetails failed - pcode: %s, error: %v", pcode, err)
		return nil, err
	}
	return details, nil
}

func (dao *pmsDaoImpl) GetUsersByOrg(orgCode string) ([]models.ProjectUser, error) {
	const query = "SELECT id,user_id,user_name,org_code,org_level,created_at FROM bsm_user WHERE org_code=?"

	var auditors []models.ProjectUser
	err := dao.dbPms.Select(&auditors, query, orgCode)
	if err != nil {
		logger.Errorf("GetUsersByOrg failed - orgCode: %s, error: %v", orgCode, err)
		return nil, err
	}
	return auditors, nil
}
